package com.szholly.pro.watergas_server.ps.android.service;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.ps.android.bean.RuntimeDataBean;
import com.szholly.pro.watergas_server.ps.android.common.NonNullUtil;
import com.szholly.pro.watergas_server.ps.android.common.ValidUtils;
import com.szholly.pro.watergas_server.ps.android.controller.SewageController;
import com.szholly.pro.watergas_server.ps.android.controller.SewageService;
import com.szholly.utils.util.StringUtils;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;

@Service
public class MoveDataService {
	private final SewageService mSewageService = SewageController.getSewageManager();

	private static final List<String> PARAMETER_LIST = Arrays.asList("011", "001", "060", "phtem");

	// 获取所有的工厂字典信息--组编号为空则获取全部数据
	public static List<RowBase> getFactoryList(String groupCode) throws Exception {
		List<GlobalParameter> parms = new ArrayList<>();
		String sewageSql = "  select distinct t1.EXP_NO \"id\",\n"
				+ "              (t1.NAME) \"name\",\n"
				+ "              (t1.mobile) \"mobile\",\n"
				+ "              (t1.contact_address) \"address\",\n"
				+ "              (t1.legal_person) \"legalPerson\",\n"
				+ "              (t1.IMG) \"imgUrl\",\n"
				+ "              (t2.S_DOMAINCAPTION) \"unit\"\n"
				+ "from BC_ENTERPRISE_ATTR t1\n"
				+ "left join (select s_id, S_DOMAINCAPTION\n"
				+ "             from DATA_DEFULATDOMAIN\n"
				+ "            where S_DOMAINNAME = '政区'\n"
				+ "              and length(s_id) = '2') t2\n"
				+ "  ON SUBSTR(t1.EXP_NO, 3, 2) = t2.s_id\n"
				+ "left join BC_SITE_INFO t3\n"
				+ "  on t3.wwtp_id = t1.exp_no\n"
				+ "left join OPERATION_GROUP_STATION_ASSO t4\n"
				+ "  on t4.station_id = t3.code\n"
				+ "WHERE t1.EXP_NO IS NOT NULL\n"
				+ " and t3.station_category = '0'\n";
		String surfaceSql = "select distinct 'surface'||t1.CODE \"id\",\n"
				+ "                t1.NAME \"name\",\n"
				+ "                n'暂无' \"mobile\",\n"
				+ "                n'暂无' \"address\",\n"
				+ "                n'暂无' \"legalPerson\",\n"
				+ "                '' \"imgUrl\",\n"
				+ "                t2.S_DOMAINCAPTION \"unit\"\n"
				+ "  from BC_SITE_INFO t1\n"
				+ "  left join (select s_id, S_DOMAINCAPTION\n"
				+ "               from DATA_DEFULATDOMAIN\n"
				+ "              where S_DOMAINNAME = '政区'\n"
				+ "                and length(s_id) = '2') t2\n"
				+ "    ON SUBSTR(t1.CODE, 3, 2) = t2.s_id\n"
				+ "  left join STATION_INFO t3\n"
				+ "    on t3.STATION_CODE = t1.CODE\n"
				+ "  left join OPERATION_GROUP_STATION_ASSO t4\n"
				+ "    on t4.station_id = t1.code\n"
				+ " where 1 = 1\n"
				+ "   and t1.station_category = '1'\n";
		if (!StringUtils.IsNullOrSpace(groupCode)) {
			parms.add(new GlobalParameter("groupCode", groupCode));
			sewageSql += "   and t4.operation_group_id = @groupCode\n";
			surfaceSql += "   and t4.operation_group_id = @groupCode\n";
		}
		String sql = sewageSql + "   union all\n" + surfaceSql;
		return NonNullUtil.toList(DataUtil.getSelectRows(sql, parms));
	}

	// 获取污水工厂列表
	public List<RowBase> getSewageFactoryList(String groupCode) {
		List<GlobalParameter> parms = new ArrayList<>();
		String sewageSql = "  select distinct t1.EXP_NO \"id\",\n"
				+ "              (t1.NAME) \"name\",\n"
				+ "              (t1.mobile) \"mobile\",\n"
				+ "              (t1.contact_address) \"address\",\n"
				+ "              (t1.legal_person) \"legalPerson\",\n"
				+ "              (t1.IMG) \"imgUrl\",\n"
				+ "              (t2.S_DOMAINCAPTION) \"unit\"\n"
				+ "from BC_ENTERPRISE_ATTR t1\n"
				+ "left join (select s_id, S_DOMAINCAPTION\n"
				+ "             from DATA_DEFULATDOMAIN\n"
				+ "            where S_DOMAINNAME = '政区'\n"
				+ "              and length(s_id) = '2') t2\n"
				+ "  ON SUBSTR(t1.EXP_NO, 3, 2) = t2.s_id\n"
				+ "left join BC_SITE_INFO t3\n"
				+ "  on t3.wwtp_id = t1.exp_no\n"
				+ "left join OPERATION_GROUP_STATION_ASSO t4\n"
				+ "  on t4.station_id = t3.code\n"
				+ "WHERE t1.EXP_NO IS NOT NULL\n"
				+ " and t3.station_category = '0'\n";
		if (!StringUtils.IsNullOrSpace(groupCode)) {
			parms.add(new GlobalParameter("groupCode", groupCode));
			sewageSql += "   and t4.operation_group_id = @groupCode\n";
		}
		return NonNullUtil.toList(DataUtil.getSelectRows(sewageSql, parms));
	}

	// 获取所有的工厂字典信息--组编号为空则获取全部数据
	public List<RowBase> getSurfaceFactoryList(String groupCode) throws Exception {
		List<GlobalParameter> parms = new ArrayList<>();
		String sql = "select distinct 'surface'||t1.CODE \"id\",\n"
				+ "                t1.NAME \"name\",\n"
				+ "                n'暂无' \"mobile\",\n"
				+ "                n'暂无' \"address\",\n"
				+ "                n'暂无' \"legalPerson\",\n"
				+ "                '' \"imgUrl\",\n"
				+ "                t2.S_DOMAINCAPTION \"unit\"\n"
				+ "  from BC_SITE_INFO t1\n"
				+ "  left join (select s_id, S_DOMAINCAPTION\n"
				+ "               from DATA_DEFULATDOMAIN\n"
				+ "              where S_DOMAINNAME = '政区'\n"
				+ "                and length(s_id) = '2') t2\n"
				+ "    ON SUBSTR(t1.CODE, 3, 2) = t2.s_id\n"
				+ "  left join STATION_INFO t3\n"
				+ "    on t3.STATION_CODE = t1.CODE\n"
				+ "  left join OPERATION_GROUP_STATION_ASSO t4\n"
				+ "    on t4.station_id = t1.code\n"
				+ " where 1 = 1\n"
				+ "   and t1.station_category = '1'\n";
		if (!StringUtils.IsNullOrSpace(groupCode)) {
			parms.add(new GlobalParameter("groupCode", groupCode));
			sql += "   and t4.operation_group_id = @groupCode\n";
		}
		return NonNullUtil.toList(DataUtil.getSelectRows(sql, parms));
	}

	// 获取所有的站点字典信息
	public static List<RowBase> getStationList(String groupCode, String type) throws Exception {
		List<GlobalParameter> parms = new ArrayList<>();
		String sql = "select distinct t1.CODE \"id\",\n"
				+ "                t1.NAME \"name\",\n"
				+ "                t1.FLOW_TYPE \"flowType\",\n"
				+ "                t1.STATION_CATEGORY \"stationCategory\",\n"
				+ "                case\n"
				+ "                  when t1.station_category = '1' then\n"
				+ "                   'surface'||t1.CODE\n"
				+ "                  else\n"
				+ "                   t1.WWTP_ID\n"
				+ "                end \"factoryId\",\n"
				+ "                t2.S_DOMAINCAPTION \"unit\",\n"
				+ "                t3.X \"x\",\n"
				+ "                t3.Y \"y\",\n"
				+ "                t1.x \"arcGisX\",\n"
				+ "                t1.Y \"arcGisY\"\n"
				+ "  from BC_SITE_INFO t1\n"
				+ "  left join (select s_id, S_DOMAINCAPTION\n"
				+ "               from DATA_DEFULATDOMAIN\n"
				+ "              where S_DOMAINNAME = '政区'\n"
				+ "                and length(s_id) = '2') t2\n"
				+ "    ON SUBSTR(t1.CODE, 3, 2) = t2.s_id\n"
				+ "  left join STATION_INFO t3\n"
				+ "    on t3.STATION_CODE = t1.CODE\n"
				+ "  left join OPERATION_GROUP_STATION_ASSO t4\n"
				+ "    on t4.station_id = t1.code\n"
				+ " where 1 = 1\n";
		if (!StringUtils.IsNullOrSpace(type)) {
			parms.add(new GlobalParameter("type", type));
			sql += "   and t1.station_category = @type\n";
		} else {
			sql += "   and t1.station_category in ('0', '1')\n";
		}
		if (!StringUtils.IsNullOrSpace(groupCode)) {
			parms.add(new GlobalParameter("groupCode", groupCode));
			sql += "   and t4.operation_group_id = @groupCode\n";
		}
		sql += " order by t2.S_DOMAINCAPTION";
		return NonNullUtil.toList(DataUtil.getSelectRows(sql, parms));
	}

	// 获取分区列表
	public static List<RowBase> getAdministrativeDivisionList() throws Exception {
		String sql = "select s_id \"id\", S_DOMAINCAPTION \"name\"\n"
				+ "  from DATA_DEFULATDOMAIN\n"
				+ " where S_DOMAINNAME = '政区'\n"
				+ "   and length(s_id) = '2'\n"
				+ "   and s_id <> '00'\n"
				+ " order by s_id";
		return NonNullUtil.toList(DataUtil.getSelectRows(sql, null));
	}

	// 获取所有的参数字典信息
	public static List<RowBase> getParameterList() throws Exception {
		String sql = "select S_DOMAINCODE \"id\", S_DOMAINCAPTION \"name\", S_UNIT \"unit\"\n"
				+ "  from data_defulatdomain\n"
				+ " where S_DOMAINNAME = 'MONITOR_PARAM'";
		return NonNullUtil.toList(DataUtil.getSelectRows(sql, null));
	}

	// 获取所有的参数字典信息
	public static List<RowBase> getUnitList() throws Exception {
		String sql = "select S_DOMAINCODE \"id\", S_DOMAINCAPTION \"name\", S_UNIT \"unit\"\n"
				+ "  from data_defulatdomain\n"
				+ " where S_DOMAINNAME = 'MONITOR_PARAM'";
		return NonNullUtil.toList(DataUtil.getSelectRows(sql, null));
	}

	// 获取关注的工厂编号列表
	public List<String> getAttentionList(String userId) throws Exception {
		ValidUtils.hasText(userId, "用户编号为空，关注信息获取失败");
		List<GlobalParameter> parms = new ArrayList<>();
		parms.add(new GlobalParameter("userId", userId));
		String sql = "select distinct STATION_CODE\n" + "  from ATTENTION_INFO t\n" + " where t.USER_ID = @userId";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		List<String> attentionStationCodeList = new ArrayList<String>();
		if (!CollectionUtils.isEmpty(rows)) {
			for (RowBase rowBase : rows) {
				attentionStationCodeList.add(rowBase.getSafeString("STATION_CODE"));
			}
		}
		return attentionStationCodeList;
	}

	// {"dateTime":"2017-04-14 16:07:33" ,"stationCode":"站点编号"
	// ,"valueList":[{"id":" ", "name":" ", "value":" ", "isAlarm":"0或1", }]}
	public List<Map<String, Object>> getRuntimeData(String factoryCode) throws Exception {
		List<Map<String, Object>> valueList = new ArrayList<>();
		List<GlobalParameter> parms = new ArrayList<>();
		boolean isSurface = factoryCode.contains("surface");
		String sql;
		if (isSurface) {
			parms.add(new GlobalParameter("factoryCode", factoryCode.substring(7)));
			sql = "select t.DEVICE_NUM, t.site_id\n" + "  from BC_COLLECTION_INFO t\n" + " where t.site_id = @factoryCode";
		} else {
			parms.add(new GlobalParameter("factoryCode", factoryCode));
			sql = "select t.DEVICE_NUM, t.site_id\n"
					+ "  from BC_COLLECTION_INFO t\n"
					+ "  left join BC_SITE_INFO t1\n"
					+ "    on t.site_id = t1.code\n"
					+ " where t1.wwtp_id = @factoryCode";
		}
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		ValidUtils.notEmpty(rows, "工厂编号有误，无法获取DeviceNumber");
		for (RowBase rowBase : rows) {
			String deviceNum = rowBase.getSafeString("DEVICE_NUM");
			String stationCode = rowBase.getSafeString("site_id");
			Map<String, Object> map = new HashMap<>();
			map.put("valueList", mSewageService.getRuntimeDataList(deviceNum));
			map.put("dateTime", mSewageService.getRuntimeDataTime(deviceNum));
			map.put("stationCode", stationCode);
			valueList.add(map);
		}
		return valueList;
	}

	/**
	 * 获取实时数据详情
	 * 
	 * 每个RowBase 包含以下信息
	 * 
	 * String leftLabel 左边的label
	 * 
	 * String rightLabel 右边的label
	 * 
	 * List<RuntimeDataBean> valueList 展示于界面上的数据
	 * 
	 * 包含两种数据(leftValue leftIsAlarm rightValue rightIsAlarm name)
	 */
	@SuppressWarnings("unchecked")
	public List<RowBase> getRuntimeDataDetail(String factoryCode) throws Exception {
		ValidUtils.hasText(factoryCode, "工厂编号为空，实时数据获取失败");
		// 返回的结果
		List<RowBase> resultList = new ArrayList<>();

		boolean isSurface = isSurface(factoryCode);

		List<RowBase> deviceList = queryDeviceList(factoryCode);
		RowBase result = null;
		String leftDateTime = null;
		for (int i = 0; i < deviceList.size(); i++) {
			RowBase deviceRow = deviceList.get(i);
			String deviceNum = deviceRow.getSafeString("DEVICE_NUM");
			String stationName = deviceRow.getSafeString("STATION_NAME");

			String label = null;
			if (isSurface) {
				label = stationName;
			} else {
				int indexOf = stationName.indexOf("水口");
				if (indexOf >= 1) {
					label = stationName.substring(indexOf - 1, stationName.length());
				}
			}

			// 实时数据
			List<Map<String, String>> runtimeDataList = mSewageService.getRuntimeDataList(deviceNum);
			// 实时数据对应的时间
			String dataTime = mSewageService.getRuntimeDataTime(deviceNum);

			boolean nowLeft = i % 2 == 0;
			Map<String, RuntimeDataBean> runtimeDataMap;
			if (nowLeft) {
				result = new RowBase();
				runtimeDataMap = new HashMap<>();
				result.put("leftLabel", label);
				result.put("valueList", runtimeDataMap);
				leftDateTime = dataTime;
				resultList.add(result);
				for (Map<String, String> value : runtimeDataList) {
					String parameterCode = value.get("id");
					RuntimeDataBean valueBean = new RuntimeDataBean();
					valueBean.setLeftValue(value.get("value"));
					valueBean.setLeftIsAlarm(value.get("isAlarm"));
					valueBean.setRightValue("-");
					valueBean.setRightIsAlarm("0");
					valueBean.setName(value.get("name"));
					runtimeDataMap.put(parameterCode, valueBean);
				}
			} else {
				runtimeDataMap = (Map<String, RuntimeDataBean>) result.get("valueList");
				result.put("rightLabel", label);
				for (Map<String, String> value : runtimeDataList) {
					String parameterCode = value.get("id");
					RuntimeDataBean valueBean;
					if (!runtimeDataMap.containsKey(parameterCode)) {
						valueBean = new RuntimeDataBean();
						valueBean.setLeftValue("-");
						valueBean.setLeftIsAlarm("0");
						valueBean.setName(value.get("name"));
						runtimeDataMap.put(parameterCode, valueBean);
					} else {
						valueBean = runtimeDataMap.get(parameterCode);
					}
					valueBean.setRightIsAlarm(value.get("isAlarm"));
					valueBean.setRightValue(value.get("value"));
				}
			}

			// 将Map转换成List
			if (!nowLeft || i == deviceList.size() - 1) {// 如果是右边或者是最后一个
				RuntimeDataBean dateTimeBean = new RuntimeDataBean();
				dateTimeBean.setName("数据时间");
				dateTimeBean.setLeftValue(leftDateTime);
				dateTimeBean.setLeftIsAlarm("0");

				List<RuntimeDataBean> valueList = new ArrayList<>(runtimeDataMap.values());
				if (!nowLeft) {// 右边
					dateTimeBean.setRightValue(dataTime);
					dateTimeBean.setRightIsAlarm("0");
				} else {
					dateTimeBean.setRightValue("-");
					dateTimeBean.setRightIsAlarm("0");
				}
				valueList.add(0, dateTimeBean);
				result.put("valueList", valueList);
			}
		}
		return resultList;
	}

	// {"dateTime":"2017-04-14 16:07:33" ,"stationCode":"站点编号"
	// ,"valueList":[{"id":" ", "name":" ", "value":" ", "isAlarm":"0或1", }]}
	public List<Map<String, Object>> getHomeRuntimeDataList(String factoryCode) throws Exception {
		List<Map<String, Object>> valueList = new ArrayList<>();
		List<RowBase> rows = queryDeviceList(factoryCode);
		for (RowBase rowBase : rows) {
			String deviceNum = rowBase.getSafeString("DEVICE_NUM");
			String stationCode = rowBase.getSafeString("site_id");
			SewageService manager = SewageController.getSewageManager();
			Map<String, Object> map = new HashMap<>();
			map.put("valueList", filterParameter(manager.getRuntimeDataList(deviceNum)));
			map.put("dateTime", manager.getRuntimeDataTime(deviceNum));
			map.put("stationCode", stationCode);
			valueList.add(map);
		}
		return valueList;
	}

	// 筛选出指定参数的数据
	public List<Map<String, String>> filterParameter(List<Map<String, String>> valueList) throws Exception {
		List<Map<String, String>> resultList = new ArrayList<>();
		if (!CollectionUtils.isEmpty(valueList)) {
			for (Map<String, String> bean : valueList) {
				String parameterCode = bean.get("id");
				if (PARAMETER_LIST.contains(parameterCode)) {
					resultList.add(bean);
				}
			}
		}
		return resultList;
	}

	// 获取所有的工厂字典信息--组编号为空则获取全部数据
	public static List<RowBase> getFactoryInitList(String userId, String groupCode) throws Exception {
		List<GlobalParameter> parms = new ArrayList<>();
		String sewageSql = "  select distinct t1.EXP_NO \"id\",\n"
				+ "              (t1.NAME) \"name\",\n"
				+ "              (t1.mobile) \"mobile\",\n"
				+ "              (t1.contact_address) \"address\",\n"
				+ "              (t1.legal_person) \"legalPerson\",\n"
				+ "              (t1.IMG) \"imgUrl\",\n"
				+ "              (t2.S_DOMAINCAPTION) \"unit\"\n"
				+ "from BC_ENTERPRISE_ATTR t1\n"
				+ "left join (select s_id, S_DOMAINCAPTION\n"
				+ "             from DATA_DEFULATDOMAIN\n"
				+ "            where S_DOMAINNAME = '政区'\n"
				+ "              and length(s_id) = '2') t2\n"
				+ "  ON SUBSTR(t1.EXP_NO, 3, 2) = t2.s_id\n"
				+ "left join BC_SITE_INFO t3\n"
				+ "  on t3.wwtp_id = t1.exp_no\n"
				+ "left join OPERATION_GROUP_STATION_ASSO t4\n"
				+ "  on t4.station_id = t3.code\n"
				+ "WHERE t1.EXP_NO IS NOT NULL\n"
				+ " and t3.station_category = '0'\n";
		String surfaceSql = "select distinct 'surface'||t1.CODE \"id\",\n"
				+ "                t1.NAME \"name\",\n"
				+ "                n'暂无' \"mobile\",\n"
				+ "                n'暂无' \"address\",\n"
				+ "                n'暂无' \"legalPerson\",\n"
				+ "                '' \"imgUrl\",\n"
				+ "                t2.S_DOMAINCAPTION \"unit\"\n"
				+ "  from BC_SITE_INFO t1\n"
				+ "  left join (select s_id, S_DOMAINCAPTION\n"
				+ "               from DATA_DEFULATDOMAIN\n"
				+ "              where S_DOMAINNAME = '政区'\n"
				+ "                and length(s_id) = '2') t2\n"
				+ "    ON SUBSTR(t1.CODE, 3, 2) = t2.s_id\n"
				+ "  left join STATION_INFO t3\n"
				+ "    on t3.STATION_CODE = t1.CODE\n"
				+ "  left join OPERATION_GROUP_STATION_ASSO t4\n"
				+ "    on t4.station_id = t1.code\n"
				+ " where 1 = 1\n"
				+ "   and t1.station_category = '1'\n";
		if (!StringUtils.IsNullOrSpace(groupCode)) {
			parms.add(new GlobalParameter("groupCode", groupCode));
			sewageSql += "   and t4.operation_group_id = @groupCode\n";
			surfaceSql += "   and t4.operation_group_id = @groupCode\n";
		}
		String sql = sewageSql + "   union all\n" + surfaceSql;
		return NonNullUtil.toList(DataUtil.getSelectRows(sql, parms));
	}

	// 获取设备信息
	private List<RowBase> queryDeviceList(String factoryCode) throws Exception {
		ValidUtils.hasText(factoryCode, "工厂编号为空，获取设备信息失败");
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		boolean isSurface = isSurface(factoryCode);
		String sql;
		if (isSurface) {
			parms.add(new GlobalParameter("factoryCode", factoryCode.substring(7)));
			sql = "select t.DEVICE_NUM, t.site_id, t1.name station_name\n"
					+ "  from BC_COLLECTION_INFO t\n"
					+ "  left join BC_SITE_INFO t1\n"
					+ "    on t.site_id = t1.code\n"
					+ " where t.site_id = @factoryCode";
		} else {
			parms.add(new GlobalParameter("factoryCode", factoryCode));
			sql = "select t.DEVICE_NUM, t.site_id, t1.name station_name\n"
					+ "  from BC_COLLECTION_INFO t\n"
					+ "  left join BC_SITE_INFO t1\n"
					+ "    on t.site_id = t1.code\n"
					+ " where t1.wwtp_id = @factoryCode";
		}
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		return NonNullUtil.toList(rows);
	}

	private boolean isSurface(String factoryCode) {
		if (StringUtils.IsNullOrSpace(factoryCode)) {
			return false;
		}
		return factoryCode.contains("surface");
	}

}
